package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.GSR;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.Store;

public class SellerTongService {

	public Record countStore() {
		return Db.findFirst("select A.tolnumber,B.sjshnumber,C.sjnumber,D.xjnumber FROM"
				+ " (select  count(T.id) as tolnumber from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id )A,"
				+ "(Select count(T.id) as sjshnumber from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id where T.state=1)B,"
				+ "(SELECT count(T.id) as sjnumber from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id where T.state=2)C,"
				+ "(SELECT count(T.id) as xjnumber from t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id where T.state=3)D");
	}

	public Page<Record> findByPage(int pageNum, int pageSize,String seller, String sellerId, String tel, String beginDate,String endDate) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_bus_seller T "
				+ "inner JOIN t_auth_admin_seller A ON T.userid = A.id "
				+ "LEFT JOIN (select sellerId, count(id) as countStore from t_dict_store GROUP BY sellerId )B on T.sellerId=B.sellerId "
				+ "LEFT JOIN (select td.sellerId ,count(distinct tb.id) as countGoods from t_bus_goods  tb INNER JOIN t_bus_goods_store_rel tt on tb.good_store=tt.goodstore INNER JOIN t_dict_store td ON td.storeid = tt.storeid GROUP BY td.sellerId ) C ON T.sellerId=C.sellerId "
				+ "LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.sellerId ) D on T.sellerId=D.sellerId "
				+ "WHERE 1=1 and T.state =2");
		List<Object> params = new ArrayList<Object>();
//		if(sellerId !=null && sellerId.length>0 ){
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND T.sellerId =? ");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(seller)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(seller);
		}
		if(!StrKit.isBlank(tel) ){
			sBuffer.append(" AND T.contact=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.createtime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
			sBuffer.append(" ORDER BY T.createtime DESC,T.id desc");
		
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.sellerId,T.contact,A.trueName,A.phonenumber,T.sellerName,T.createtime,T.checktime,T.power,ifnull(B.countStore,0) countStore,ifnull(C.countGoods,0) countGoods,ifnull(D.countMoney,0) countMoney,ifnull(D.countSell,0) countSell", sBuffer.toString(), params.toArray());
	}
/***
 * 排序
 * @param pageNum
 * @param pageSize
 * @param index
 * @param seller
 * @param sellerId
 * @param tel
 * @param beginDate
 * @param endDate
 * @return
 */
	public Page<Record> findByPage1(int pageNum, int pageSize,String index,String seller, String sellerId, String tel, String beginDate,String endDate) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_bus_seller T "
				+ "inner JOIN t_auth_admin_seller A ON T.userid = A.id "
				+ "LEFT JOIN (select sellerId, count(id) as countStore from t_dict_store GROUP BY sellerId )B on T.sellerId=B.sellerId "
				+ "LEFT JOIN (select td.sellerId ,count(distinct tb.id) as countGoods from t_bus_goods  tb INNER JOIN t_bus_goods_store_rel tt on tb.good_store=tt.goodstore INNER JOIN t_dict_store td ON td.storeid = tt.storeid GROUP BY td.sellerId ) C ON T.sellerId=C.sellerId "
				+ "LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
				+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
				+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
				+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
				+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.sellerId ) D on T.sellerId=D.sellerId "
				+ "WHERE 1=1 and T.state =2");
		List<Object> params = new ArrayList<Object>();
//		if(sellerId !=null && sellerId.length>0 ){
		if(!StrKit.isBlank(sellerId)){
			sBuffer.append(" AND T.sellerId =? ");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(seller)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(seller);
		}
		if(!StrKit.isBlank(tel) ){
			sBuffer.append(" AND T.contact=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.createtime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		//订单量”和“销售额”排序，可由下到大／由大到小排序
		if(!StrKit.isBlank(index)){
			switch (index) {
				case "1":
					sBuffer.append(" GROUP BY T.createtime ORDER BY D.countSell DESC");
					break;
				case "2":
					sBuffer.append(" GROUP BY T.createtime ORDER BY D.countSell ASC");
					break;
				case "3":
					sBuffer.append(" GROUP BY T.createtime ORDER BY D.countMoney DESC");
					break;
				case "4":
					sBuffer.append(" GROUP BY T.createtime ORDER BY D.countMoney ASC");
					break;	
				}	
		}	
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.sellerId,T.contact,A.trueName,A.phonenumber,T.sellerName,T.createtime,T.checktime,T.power,ifnull(B.countStore,0) countStore,ifnull(C.countGoods,0) countGoods,ifnull(D.countMoney,0) countMoney,ifnull(D.countSell,0) countSell", sBuffer.toString(), params.toArray());
	}
	public Page<Record> findXSByPage(int pageNum, int pageSize, String seller, String sellerId, String tel, String beginDate,String endDate,int date) {
		StringBuffer sBuffer = new StringBuffer(" FROM t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id LEFT JOIN (select sellerId, count(id) as countStore from t_dict_store GROUP BY sellerId )B on T.sellerId=B.sellerId"
				+ " LEFT JOIN (select td.sellerId ,count(tb.id) as countGoods from t_bus_goods  tb INNER JOIN t_bus_goods_store_rel tt on tb.good_store=tt.goodstore INNER JOIN t_dict_store td ON td.storeid = tt.storeid GROUP BY td.sellerId ) C ON T.sellerId=C.sellerId ");
		switch (date) {
			case 1://当日
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  and to_days(tbo.payDate) =to_days(now()) GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			case 2://本周
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   and YEARWEEK(date_format(tbo.payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			case 3://本月
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   and date_format(tbo.payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			default:
				//默认全部
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.sellerId ) D on T.sellerId=D.sellerId "
						+ "WHERE 1=1 and T.state =2");
				break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId) && !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(seller)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(seller);
		}
		if(!StrKit.isBlank(tel) ){
			sBuffer.append(" AND T.contact=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.createtime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY countMoney desc, T.createtime DESC");
		return Db.paginate(pageNum, pageSize, "SELECT T.id,T.sellerId,T.contact,A.trueName,A.phonenumber,T.sellerName,T.createtime,T.checktime,T.power,ifnull(B.countStore,0) countStore,ifnull(C.countGoods,0) countGoods,ifnull(D.countMoney,0) countMoney,ifnull(D.countSell,0) countSell", sBuffer.toString(), params.toArray());
	}
	public Record findById(String sellerId) {
		return Db.findFirst("select sellerId,sellerName,power from t_bus_seller where sellerId=?",sellerId);
	}

	public Seller findByStoreId(String sellerId) {
		return Seller.me().findFirst("select * from t_bus_seller where sellerId=?",sellerId);
	}

	public Seller findPageBySellerId(String sellerId) {
		return Seller.me().findFirst("SELECT T.id,T.sellerId,T.contact,A.trueName,A.phonenumber,T.sellerName,T.createtime,T.checktime,T.power,ifnull(B.countStore,0) countStore,ifnull(C.countGoods,0) countGoods,ifnull(D.countMoney,0) countMoney,ifnull(D.countSell,0) countSell "
				+ "FROM t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id LEFT JOIN (select sellerId, count(id) as countStore from t_dict_store GROUP BY sellerId )B "
				+ "on T.sellerId=B.sellerId LEFT JOIN (select td.sellerId ,count(tb.id) as countGoods from t_bus_goods  tb INNER JOIN t_bus_goods_store_rel tt on tb.good_store=tt.goodstore INNER JOIN t_dict_store td ON td.storeid = tt.storeid GROUP BY td.sellerId ) C "
				+ "ON T.sellerId=C.sellerId LEFT JOIN (select ts.sellerId ,sum(tbo.amount) as countMoney,sum(tbo.countnum)as countSell from t_bus_order tbo inner JOIN t_dict_store ts on tbo.storeid=ts.storeid where tbo.payState=2  GROUP BY ts.sellerId ) D "
				+ "on T.sellerId=D.sellerId WHERE T.sellerId=?",sellerId);
	}
	public boolean edit(Seller seller) {
		return seller.update();
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "公司统计列表", titles, listz);
	}

	public List<Record> findExcelByPage(String seller, String sellerId, String tel, String beginDate, String endDate,int date) {
		StringBuffer sBuffer = new StringBuffer("SELECT T.sellerId,T.contact,A.trueName,A.phonenumber,T.sellerName,T.createtime,T.checktime,T.power,ifnull(B.countStore,0) countStore,ifnull(C.countGoods,0) countGoods,ifnull(D.countMoney,0) countMoney,ifnull(D.countSell,0) countSell"
				+ " FROM t_bus_seller T inner JOIN t_auth_admin_seller A ON T.userid = A.id LEFT JOIN (select sellerId, count(id) as countStore from t_dict_store GROUP BY sellerId )B on T.sellerId=B.sellerId"
				+ " LEFT JOIN (select td.sellerId ,count(tb.id) as countGoods from t_bus_goods  tb INNER JOIN t_bus_goods_store_rel tt on tb.good_store=tt.goodstore INNER JOIN t_dict_store td ON td.storeid = tt.storeid GROUP BY td.sellerId ) C ON T.sellerId=C.sellerId ");
		switch (date) {
			case 1://当日
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   and to_days(tbo.payDate) =to_days(now()) GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			case 2://本周
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   and YEARWEEK(date_format(tbo.payDate,'%Y-%m-%d'))= YEARWEEK(now()) GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			case 3://本月
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   and date_format(tbo.payDate,'%Y-%m')=date_format(now(),'%Y-%m') GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
			default:
				//默认全部
				sBuffer.append("LEFT JOIN (select ts.sellerId ,sum(tbol.amount) as countMoney,sum(tbol.count)as countSell from t_bus_order_list tbol "
						+ "left join t_bus_order tbo on tbol.orderNum=tbo.orderNum "
						+ "left join t_bus_goods tbg on tbg.id = tbol.goodsid  "
						+ "left join t_bus_goods_store_rel tbgsr on tbgsr.goodstore = tbg.good_store "
						+ "inner JOIN t_dict_store ts on tbgsr.storeid=ts.storeid where tbo.payState=2   GROUP BY ts.sellerId ) D "
						+ "on T.sellerId=D.sellerId WHERE 1=1 and T.state =2");
				break;
		}
		
		List<Object> params = new ArrayList<Object>();
		if(!StrKit.isBlank(sellerId) && !"0".equals(sellerId)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(sellerId);
		}
		if(!StrKit.isBlank(seller)){
			sBuffer.append(" AND T.sellerId=?");
			params.add(seller);
		}
		if(!StrKit.isBlank(tel) ){
			sBuffer.append(" AND T.contact=?");
			params.add(tel);
		}
		if(!StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND T.createtime BETWEEN ? AND ?");
			params.add(beginDate);
			params.add(endDate);
		}else if(!StrKit.isBlank(beginDate)&&StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')>=?");
			params.add(beginDate);
		}else if(StrKit.isBlank(beginDate)&&!StrKit.isBlank(endDate)){
			sBuffer.append(" AND DATE_FORMAT(T.createtime,'%Y-%m-%d')<=?");
			params.add(endDate);
		}
		sBuffer.append(" ORDER BY T.createtime DESC,T.id desc");
		return Db.find(sBuffer.toString(), params.toArray());
	}
	public List<Store> findStoreBySeller(String sellerId) {
		return Store.me().find("select * from t_dict_store where sellerId=?",sellerId);
	}
	public List<GSR> finds(String storeid) {
		return GSR.me().find("select * from t_bus_goods_store_rel where storeid=?",storeid);
	}
	
}
